# -*- codeing = utf-8 -*-
# @Time: 2021/11/8 22:37
# @Author: Foxhuty
# @File: junior_scores.py
# @Software: PyCharm
# @Based on python 3.9

import pandas as pd
import numpy as np
import os


# file = r'D:\work documents\初中\初2020级八上半期成绩.xlsx'
# df = pd.read_excel(file, sheet_name='总表', index_col='序号')
# av_df = pd.read_excel(file, sheet_name='初中表', index_col='序号')


class JuniorExam(object):
    def __init__(self, file_path):
        self.file_path = file_path
        self.df = pd.read_excel(file_path, sheet_name='总表')

    def __str__(self):
        return f'正在对{os.path.basename(self.file_path)}进行成绩分析处理。'

    def junior_scores(self):
        """
        计算初中考试合格人数及合格率
        :return: 生成一个excel文件
        """
        if '化学' in self.df.columns:
            qualification_df = self.df.loc[:,
                               ['序号', '姓名', '班级', '语文A卷', '数学A卷', '英语A卷', '物理A卷', '化学', '总分']]
            # qualification_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '化学', '总分']

            # 计算合格人数
            single_chn = qualification_df[qualification_df['语文A卷'] >= 60].groupby(['班级'])['语文A卷'].count()
            single_math = qualification_df[qualification_df['数学A卷'] >= 60].groupby(['班级'])['数学A卷'].count()
            single_eng = qualification_df[qualification_df['英语A卷'] >= 60].groupby(['班级'])['英语A卷'].count()
            single_phys = qualification_df[qualification_df['物理A卷'] >= 60].groupby(['班级'])['物理A卷'].count()
            single_chem = qualification_df[qualification_df['化学'] >= 60].groupby(['班级'])['化学'].count()

            name_num = qualification_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            qualified_data = pd.concat([name_num, single_chn, single_math, single_eng,
                                        single_phys, single_chem],
                                       axis=1)

            qualified_data.loc['年级'] = [qualified_data['参考人数'].sum(),
                                          qualified_data['语文A卷'].sum(),
                                          qualified_data['数学A卷'].sum(),
                                          qualified_data['英语A卷'].sum(),
                                          qualified_data['物理A卷'].sum(),
                                          qualified_data['化学'].sum()]
            # 计算合格率
            qualified_data['语文合格率'] = qualified_data['语文A卷'] / qualified_data['参考人数']
            qualified_data['语文合格率'] = qualified_data['语文合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['数学合格率'] = qualified_data['数学A卷'] / qualified_data['参考人数']
            qualified_data['数学合格率'] = qualified_data['数学合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['英语合格率'] = qualified_data['英语A卷'] / qualified_data['参考人数']
            qualified_data['英语合格率'] = qualified_data['英语合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['物理合格率'] = qualified_data['物理A卷'] / qualified_data['参考人数']
            qualified_data['物理合格率'] = qualified_data['物理合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['化学合格率'] = qualified_data['化学'] / qualified_data['参考人数']
            qualified_data['化学合格率'] = qualified_data['化学合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data.reset_index(inplace=True)
            new_columns = ['班级', '参考人数', '语文A卷', '语文合格率', '数学A卷', '数学合格率', '英语A卷',
                           '英语合格率',
                           '物理A卷', '物理合格率', '化学', '化学合格率']
            qualified_data = qualified_data[new_columns]

            return qualified_data
        elif '物理A卷' not in self.df.columns:
            qualification_df = self.df.loc[:, ['序号', '姓名', '班级', '语文A卷', '数学A卷', '英语A卷', '总分']]
            # qualification_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '总分']

            # 计算合格人数
            single_chn = qualification_df[qualification_df['语文A卷'] >= 60].groupby(['班级'])['语文A卷'].count()
            single_math = qualification_df[qualification_df['数学A卷'] >= 60].groupby(['班级'])['数学A卷'].count()
            single_eng = qualification_df[qualification_df['英语A卷'] >= 60].groupby(['班级'])['英语A卷'].count()

            name_num = qualification_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            qualified_data = pd.concat([name_num, single_chn, single_math, single_eng], axis=1)

            qualified_data.loc['年级'] = [qualified_data['参考人数'].sum(),
                                          qualified_data['语文A卷'].sum(),
                                          qualified_data['数学A卷'].sum(),
                                          qualified_data['英语A卷'].sum(),
                                          ]
            # 计算合格率
            qualified_data['语文合格率'] = qualified_data['语文A卷'] / qualified_data['参考人数']
            qualified_data['语文合格率'] = qualified_data['语文合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['数学合格率'] = qualified_data['数学A卷'] / qualified_data['参考人数']
            qualified_data['数学合格率'] = qualified_data['数学合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['英语合格率'] = qualified_data['英语A卷'] / qualified_data['参考人数']
            qualified_data['英语合格率'] = qualified_data['英语合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data.reset_index(inplace=True)
            new_columns = ['班级', '参考人数', '语文A卷', '语文合格率', '数学A卷', '数学合格率', '英语A卷',
                           '英语合格率']
            qualified_data = qualified_data[new_columns]

            return qualified_data
        else:
            qualification_df = self.df.loc[:,
                               ['序号', '姓名', '班级', '语文A卷', '数学A卷', '英语A卷', '物理A卷', '总分']]
            # qualification_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '总分']

            # 计算合格人数
            single_chn = qualification_df[qualification_df['语文A卷'] >= 60].groupby(['班级'])['语文A卷'].count()
            single_math = qualification_df[qualification_df['数学A卷'] >= 60].groupby(['班级'])['数学A卷'].count()
            single_eng = qualification_df[qualification_df['英语A卷'] >= 60].groupby(['班级'])['英语A卷'].count()
            single_phys = qualification_df[qualification_df['物理A卷'] >= 30].groupby(['班级'])['物理A卷'].count()

            name_num = qualification_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            qualified_data = pd.concat([name_num, single_chn, single_math, single_eng,
                                        single_phys], axis=1)

            qualified_data.loc['年级'] = [qualified_data['参考人数'].sum(),
                                          qualified_data['语文A卷'].sum(),
                                          qualified_data['数学A卷'].sum(),
                                          qualified_data['英语A卷'].sum(),
                                          qualified_data['物理A卷'].sum()]
            # 计算合格率
            qualified_data['语文合格率'] = qualified_data['语文A卷'] / qualified_data['参考人数']
            qualified_data['语文合格率'] = qualified_data['语文合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['数学合格率'] = qualified_data['数学A卷'] / qualified_data['参考人数']
            qualified_data['数学合格率'] = qualified_data['数学合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['英语合格率'] = qualified_data['英语A卷'] / qualified_data['参考人数']
            qualified_data['英语合格率'] = qualified_data['英语合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data['物理合格率'] = qualified_data['物理A卷'] / qualified_data['参考人数']
            qualified_data['物理合格率'] = qualified_data['物理合格率'].apply(lambda x: format(x, '.2%'))

            qualified_data.reset_index(inplace=True)
            new_columns = ['班级', '参考人数', '语文A卷', '语文合格率', '数学A卷', '数学合格率', '英语A卷',
                           '英语合格率',
                           '物理A卷', '物理合格率']
            qualified_data = qualified_data[new_columns]

            return qualified_data

    def get_av(self):
        # 计算平均分
        if '化学' in self.df.columns:

            av_class = self.df.groupby(['班级'])[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                                  '英语A卷', '英语B卷', '英语合卷', '物理A卷', '物理B卷', '物理合卷',
                                                  '化学', '总分']].mean().round(2)
            av_general = self.df[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                  '英语A卷', '英语B卷', '英语合卷', '物理A卷', '物理B卷', '物理合卷',
                                  '化学', '总分']].apply(np.nanmean, axis=0).round(2)
            # av_general.name = '年级平均分'
            av_class.loc['年级平均分'] = av_general
            # av_scores = av_class.append(av_general)
            av_scores = av_class
            return av_scores
        elif '物理合卷' not in self.df.columns:
            av_class = self.df.groupby(['班级'])[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                                  '英语A卷', '英语B卷', '英语合卷', '总分']].mean().round(2)
            av_general = self.df[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                  '英语A卷', '英语B卷', '英语合卷', '总分']].apply(np.nanmean, axis=0).round(2)
            # av_general.name = '年级平均分'
            av_class.loc['年级平均分'] = av_general
            # av_scores = av_class.append(av_general)
            av_scores = av_class
            return av_scores
        else:
            av_class = self.df.groupby(['班级'])[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                                  '英语A卷', '英语B卷', '英语合卷',
                                                  '物理A卷', '物理B卷', '物理合卷', '总分']].mean().round(2)
            av_general = self.df[['语文A卷', '语文B卷', '语文合卷', '数学A卷', '数学B卷', '数学合卷',
                                  '英语A卷', '英语B卷', '英语合卷', '物理A卷',
                                  '物理B卷', '物理合卷', '总分']].apply(np.nanmean, axis=0).round(2)
            # av_general.name = '年级平均分'
            av_class.loc['年级平均分']=av_general
            # av_scores = av_class.append(av_general)
            av_scores=av_class
            return av_scores

    def get_goodscores(self, goodtotal):
        """
        计算各科有效分
        goodtotal:划线总分，高线，中线，低线
        """
        if '化学' in self.df.columns:
            good_score_df = self.df.loc[:,
                            ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '物理合卷', '化学', '总分']]
            # good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '化学', '总分']

            goodscoredata = good_score_df.loc[good_score_df['总分'] >= goodtotal]
            chnav = goodscoredata['语文合卷'].mean()
            mathav = goodscoredata['数学合卷'].mean()
            engav = goodscoredata['英语合卷'].mean()
            phyav = goodscoredata['物理合卷'].mean()
            chemav = goodscoredata['化学'].mean()
            totalav = goodscoredata['总分'].mean()
            factor = goodtotal / totalav
            chn = round(chnav * factor)
            math = round(mathav * factor)
            eng = round(engav * factor)
            phy = round(phyav * factor)
            chem = round(chemav * factor)
            if (chn + math + eng + phy + chem) > goodtotal:
                math -= 1
            if (chn + math + eng + phy + chem) < goodtotal:
                chn += 1
            return chn, math, eng, phy, chem, goodtotal

        elif '物理合卷' not in self.df.columns:
            good_score_df = self.df.loc[:, ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '总分']]
            # good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '总分']

            goodscoredata = good_score_df.loc[good_score_df['总分'] >= goodtotal]
            chnav = goodscoredata['语文合卷'].mean()
            mathav = goodscoredata['数学合卷'].mean()
            engav = goodscoredata['英语合卷'].mean()
            totalav = goodscoredata['总分'].mean()

            factor = goodtotal / totalav

            chn = round(chnav * factor)
            math = round(mathav * factor)
            eng = round(engav * factor)

            if (chn + math + eng) > goodtotal:
                math -= 1
            if (chn + math + eng) < goodtotal:
                chn += 1
            return chn, math, eng, goodtotal

        else:
            good_score_df = self.df.loc[:,
                            ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '物理合卷', '总分']]
            # good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '总分']
            goodscoredata = good_score_df.loc[good_score_df['总分'] >= goodtotal]
            chnav = goodscoredata['语文合卷'].mean()
            mathav = goodscoredata['数学合卷'].mean()
            engav = goodscoredata['英语合卷'].mean()
            phyav = goodscoredata['物理合卷'].mean()
            totalav = goodscoredata['总分'].mean()
            factor = goodtotal / totalav
            chn = round(chnav * factor)
            math = round(mathav * factor)
            eng = round(engav * factor)
            phy = round(phyav * factor)
            if (chn + math + eng + phy) > goodtotal:
                math -= 1
            if (chn + math + eng + phy) < goodtotal:
                chn += 1
            return chn, math, eng, phy, goodtotal

    def goodscore_process(self, goodtotal):
        """
        计算各科各班单有效和双有效人数
        """
        # 计算各班有效学生人数

        if '化学' in self.df.columns:
            good_score_df = self.df.loc[:,
                            ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '物理合卷', '化学', '总分']]
            good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '化学', '总分']

            chn, math, eng, phy, chem, total = self.get_goodscores(goodtotal)
            single_chn, double_chn = self.get_single_double_score(good_score_df, '语文', chn, total)
            single_math, double_math = self.get_single_double_score(good_score_df, '数学', math, total)
            single_eng, double_eng = self.get_single_double_score(good_score_df, '英语', eng, total)
            single_phy, double_phy = self.get_single_double_score(good_score_df, '物理', phy, total)
            single_chem, double_chem = self.get_single_double_score(good_score_df, '化学', chem, total)
            single_total, double_total = self.get_single_double_score(good_score_df, '总分', total, total)

            # 计算参考人数
            name_num = good_score_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            goodscore_dict = {'参考人数': ' ', '语文': chn, '数学': math, '英语': eng,
                              '物理': phy, '化学': chem, '总分': total}
            goodscore_df = pd.DataFrame(goodscore_dict, index=['有效分数'])

            result_single = pd.concat([name_num, single_chn, single_math, single_eng,
                                       single_phy, single_chem, single_total],
                                      axis=1)

            result_double = pd.concat(
                [name_num, double_chn, double_math, double_eng,
                 double_phy, double_chem, double_total], axis=1)

            result_single.loc['共计'] = [result_single['参考人数'].sum(),
                                         result_single['语文'].sum(),
                                         result_single['数学'].sum(),
                                         result_single['英语'].sum(),
                                         result_single['物理'].sum(),
                                         result_single['化学'].sum(),
                                         result_single['总分'].sum()
                                         ]
            # 新增上线率一列并用百分数表示
            result_single['上线率'] = result_single['总分'] / result_single['参考人数']
            result_single['上线率'] = result_single['上线率'].apply(lambda x: format(x, '.2%'))
            # 新增一行文科共计。
            result_double.loc['共计'] = [result_double['参考人数'].sum(),
                                         result_double['语文'].sum(),
                                         result_double['数学'].sum(),
                                         result_double['英语'].sum(),
                                         result_double['物理'].sum(),
                                         result_double['化学'].sum(),
                                         result_double['总分'].sum()
                                         ]
            final_result = pd.concat([goodscore_df, result_single, result_double])

            return final_result
        elif '物理合卷' not in self.df.columns:
            good_score_df = self.df.loc[:, ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '总分']]
            good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '总分']

            chn, math, eng, total = self.get_goodscores(goodtotal)
            single_chn, double_chn = self.get_single_double_score(good_score_df, '语文', chn, total)
            single_math, double_math = self.get_single_double_score(good_score_df, '数学', math, total)
            single_eng, double_eng = self.get_single_double_score(good_score_df, '英语', eng, total)
            single_total, double_total = self.get_single_double_score(good_score_df, '总分', total, total)

            # 计算参考人数
            name_num = good_score_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            goodscore_dict = {'参考人数': ' ', '语文': chn, '数学': math, '英语': eng, '总分': total}
            goodscore_df = pd.DataFrame(goodscore_dict, index=['有效分数'])

            result_single = pd.concat([name_num, single_chn, single_math, single_eng, single_total], axis=1)

            result_double = pd.concat(
                [name_num, double_chn, double_math, double_eng, double_total], axis=1)

            result_single.loc['共计'] = [result_single['参考人数'].sum(),
                                         result_single['语文'].sum(),
                                         result_single['数学'].sum(),
                                         result_single['英语'].sum(),
                                         result_single['总分'].sum()]
            # 新增上线率一列并用百分数表示
            result_single['上线率'] = result_single['总分'] / result_single['参考人数']
            result_single['上线率'] = result_single['上线率'].apply(lambda x: format(x, '.2%'))
            # 新增一行文科共计。
            result_double.loc['共计'] = [result_double['参考人数'].sum(),
                                         result_double['语文'].sum(),
                                         result_double['数学'].sum(),
                                         result_double['英语'].sum(),
                                         result_double['总分'].sum()]
            final_result = pd.concat([goodscore_df, result_single, result_double])

            return final_result

        else:
            good_score_df = self.df.loc[:,
                            ['序号', '姓名', '班级', '语文合卷', '数学合卷', '英语合卷', '物理合卷', '总分']]
            good_score_df.columns = ['序号', '姓名', '班级', '语文', '数学', '英语', '物理', '总分']
            chn, math, eng, phy, total = self.get_goodscores(goodtotal)
            single_chn, double_chn = self.get_single_double_score(good_score_df, '语文', chn, total)
            single_math, double_math = self.get_single_double_score(good_score_df, '数学', math, total)
            single_eng, double_eng = self.get_single_double_score(good_score_df, '英语', eng, total)
            single_phy, double_phy = self.get_single_double_score(good_score_df, '物理', phy, total)
            single_total, double_total = self.get_single_double_score(good_score_df, '总分', total, total)

            # 计算参考人数
            name_num = good_score_df.groupby(['班级'])['姓名'].count()
            name_num.name = '参考人数'

            goodscore_dict = {'参考人数': ' ', '语文': chn, '数学': math, '英语': eng,
                              '物理': phy, '总分': total}
            goodscore_df = pd.DataFrame(goodscore_dict, index=['有效分数'])

            result_single = pd.concat([name_num, single_chn, single_math, single_eng,
                                       single_phy, single_total],
                                      axis=1)

            result_double = pd.concat(
                [name_num, double_chn, double_math, double_eng,
                 double_phy, double_total], axis=1)

            result_single.loc['共计'] = [result_single['参考人数'].sum(),
                                         result_single['语文'].sum(),
                                         result_single['数学'].sum(),
                                         result_single['英语'].sum(),
                                         result_single['物理'].sum(),
                                         result_single['总分'].sum()
                                         ]
            # 新增上线率一列并用百分数表示
            result_single['上线率'] = result_single['总分'] / result_single['参考人数']
            result_single['上线率'] = result_single['上线率'].apply(lambda x: format(x, '.2%'))
            # 新增一行文科共计。
            result_double.loc['共计'] = [result_double['参考人数'].sum(),
                                         result_double['语文'].sum(),
                                         result_double['数学'].sum(),
                                         result_double['英语'].sum(),
                                         result_double['物理'].sum(),
                                         result_double['总分'].sum()
                                         ]
            final_result = pd.concat([goodscore_df, result_single, result_double])

            return final_result

    def concat_files(self):
        total = eval(input(f'请输入上线总分：'))
        good_scores = self.goodscore_process(total)
        junior_qualification = self.junior_scores()
        junior_av = self.get_av()
        with pd.ExcelWriter(r'D:\成绩统计结果\初中成绩统计分析.xlsx') as writer:
            good_scores.to_excel(writer, sheet_name='有效分')
            junior_qualification.to_excel(writer, sheet_name='合格率', index=False)
            junior_av.to_excel(writer, sheet_name='平均分')
        print(f'successfully done')

    @staticmethod
    def get_single_double_score(data, subject, subject_score, total_score):
        single = data[data[subject] >= subject_score].groupby(['班级'])[subject].count()
        data_double = data[data['总分'] >= total_score]
        double = data_double[data_double[subject] >= subject_score].groupby(['班级'])[subject].count()
        return single, double


if __name__ == '__main__':
    file_path = r'D:\年级管理数据\高2021级\高二下\八年级上期末考试成绩表1111.xlsx'
    junior_exam = JuniorExam(file_path)
    print(junior_exam)
    junior_exam.concat_files()
